﻿Imports System.Data.OleDb

Module ModuleVentas
    Public Class C_todosVentas
        '**********************************************************************************************************************
        'Carga de opciones y valores de las caract a evaluar en la llamada
        '**********************************************************************************************************************

        '+++++++++++++ Correspondiente a la tabla saludo_vta +++++++++++++
        Public Function optSaludo() As OleDbDataReader
            Using cadenaConexBD()
                Dim droptS As OleDbDataReader
                Dim instruccion As New OleDbCommand("select id_salu,opt_salu,vaop_salu from saludo_vta", cadenaConexBD)
                droptS = instruccion.ExecuteReader()
                Return droptS
                droptS.Close()
            End Using
        End Function
        Public Function valorSalu(ByVal formCombo As ComboBox) As String
            Try
                cadenaConexBD()
                Dim instruccion As New OleDbCommand("select vaop_salu from saludo_vta where opt_salu = '" & formCombo.SelectedItem & "'", cadenaConexBD)
                Dim obj As Object = instruccion.ExecuteScalar()
                valorSalu = CType(obj, String)
                If valorSalu Is Nothing Then
                    valorSalu = "Inexistente"
                End If
            Catch ex As Exception
                valorSalu = "Error"
            Finally
                cadenaConexBD.Close()
            End Try
        End Function

        '+++++++++++++ Correspondiente a la tabla empatia_vta +++++++++++++
        Public Function optEmpatia() As OleDbDataReader
            Using cadenaConexBD()
                Dim droptEmpa As OleDbDataReader
                Dim instruccion As New OleDbCommand("select id_emp,opt_emp,vaop_emp from empatia_vta", cadenaConexBD)
                droptEmpa = instruccion.ExecuteReader()
                Return droptEmpa
                droptEmpa.Close()
            End Using
        End Function
        Public Function valorEmpatia(ByVal formCombo As ComboBox) As String
            Try
                cadenaConexBD()
                Dim instruccion As New OleDbCommand("select vaop_emp from empatia_vta where opt_emp = '" & formCombo.SelectedItem & "'", cadenaConexBD)
                Dim obj As Object = instruccion.ExecuteScalar()
                valorEmpatia = CType(obj, String)
                If valorEmpatia Is Nothing Then
                    valorEmpatia = "Inexistente"
                End If
            Catch ex As Exception
                valorEmpatia = "Error"
            Finally
                cadenaConexBD.Close()
            End Try
        End Function

        '+++++++++++++ Correspondiente a la tabla indagacion_vta +++++++++++++
        Public Function optIndagacion() As OleDbDataReader
            Using cadenaConexBD()
                Dim droptind As OleDbDataReader
                Dim instruccion As New OleDbCommand("select id_ind,opt_ind,vaop_ind from indagacion_vta", cadenaConexBD)
                droptind = instruccion.ExecuteReader()
                Return droptind
                droptind.Close()
            End Using
        End Function
        Public Function valorIndagacion(ByVal formCombo As ComboBox) As String
            Try
                cadenaConexBD()
                Dim instruccion As New OleDbCommand("select vaop_ind from indagacion_vta where opt_ind = '" & formCombo.SelectedItem & "'", cadenaConexBD)
                Dim obj As Object = instruccion.ExecuteScalar()
                valorIndagacion = CType(obj, String)
                If valorIndagacion Is Nothing Then
                    valorIndagacion = "Inexistente"
                End If
            Catch ex As Exception
                valorIndagacion = "Error"
            Finally
                cadenaConexBD.Close()
            End Try
        End Function

        '+++++++++++++ Correspondiente a la tabla manejo_vta +++++++++++++
        Public Function optManejo() As OleDbDataReader

            Using cadenaConexBD()
                Dim droptmanejo As OleDbDataReader
                Dim instruccion As New OleDbCommand("select id_mane,opt_mane,vaop_mane from manejo_vta", cadenaConexBD)
                droptmanejo = instruccion.ExecuteReader()
                Return droptmanejo
                droptmanejo.Close()
            End Using
        End Function
        Public Function valorManejo(ByVal formCombo As ComboBox) As String
            Try
                cadenaConexBD()
                Dim instruccion As New OleDbCommand("select vaop_mane from manejo_vta where opt_mane = '" & formCombo.SelectedItem & "'", cadenaConexBD)
                Dim obj As Object = instruccion.ExecuteScalar()
                valormanejo = CType(obj, String)
                If valormanejo Is Nothing Then
                    valormanejo = "Inexistente"
                End If
            Catch ex As Exception
                valormanejo = "Error"
            Finally
                cadenaConexBD.Close()
            End Try
        End Function

        '+++++++++++++ Correspondiente a la tabla entusiasmo_vta +++++++++++++
        Public Function optEntusiasmo() As OleDbDataReader
            Using cadenaConexBD()
                Dim droptComp As OleDbDataReader
                Dim instruccion As New OleDbCommand("select id_entu,opt_entu,vaop_entu from entusiasmo_vta", cadenaConexBD)
                droptComp = instruccion.ExecuteReader()
                Return droptComp
                droptComp.Close()
            End Using
        End Function
        Public Function valorEntusiasmo(ByVal formCombo As ComboBox) As String
            Try
                cadenaConexBD()
                Dim instruccion As New OleDbCommand("select vaop_entu from entusiasmo_vta where opt_entu = '" & formCombo.SelectedItem & "'", cadenaConexBD)
                Dim obj As Object = instruccion.ExecuteScalar()
                valorEntusiasmo = CType(obj, String)
                If valorEntusiasmo Is Nothing Then
                    valorEntusiasmo = "Inexistente"
                End If
            Catch ex As Exception
                valorEntusiasmo = "Error"
            Finally
                cadenaConexBD.Close()
            End Try
        End Function

        '+++++++++++++ Correspondiente a la tabla speech_vta +++++++++++++
        Public Function optSpeech() As OleDbDataReader
            Using cadenaConexBD()
                Dim droptinfo As OleDbDataReader
                Dim instruccion As New OleDbCommand("select id_spe,opt_spe,vaop_spe from speech_vta", cadenaConexBD)
                droptinfo = instruccion.ExecuteReader()
                Return droptinfo
                droptinfo.Close()
            End Using
        End Function
        Public Function valorSpeech(ByVal formCombo As ComboBox) As String
            Try
                cadenaConexBD()
                Dim instruccion As New OleDbCommand("select vaop_spe from speech_vta where opt_spe = '" & formCombo.SelectedItem & "'", cadenaConexBD)
                Dim obj As Object = instruccion.ExecuteScalar()
                valorSpeech = CType(obj, String)
                If valorSpeech Is Nothing Then
                    valorSpeech = "Inexistente"
                End If
            Catch ex As Exception
                valorSpeech = "Error"
            Finally
                cadenaConexBD.Close()
            End Try
        End Function

        '+++++++++++++ Correspondiente a la tabla presentacion_vta +++++++++++++
        Public Function optPresentacion() As OleDbDataReader
            Using cadenaConexBD()
                Dim droptComuni As OleDbDataReader
                Dim instruccion As New OleDbCommand("select id_pres,opt_pres,vaop_pres from presentacion_vta", cadenaConexBD)
                droptComuni = instruccion.ExecuteReader()
                Return droptComuni
                droptComuni.Close()
            End Using
        End Function
        Public Function valorPresentacion(ByVal formCombo As ComboBox) As String
            Try
                cadenaConexBD()
                Dim instruccion As New OleDbCommand("select vaop_pres from presentacion_vta where opt_pres = '" & formCombo.SelectedItem & "'", cadenaConexBD)
                Dim obj As Object = instruccion.ExecuteScalar()
                valorPresentacion = CType(obj, String)
                If valorPresentacion Is Nothing Then
                    valorPresentacion = "Inexistente"
                End If
            Catch ex As Exception
                valorPresentacion = "Error"
            Finally
                cadenaConexBD.Close()
            End Try
        End Function

        '+++++++++++++ Correspondiente a la tabla mecanismo_vta +++++++++++++
        Public Function optMecanismo() As OleDbDataReader
            Using cadenaConexBD()
                Dim droptCredi As OleDbDataReader
                Dim instruccion As New OleDbCommand("select id_mec,opt_mec,vaop_mec from mecanismo_vta", cadenaConexBD)
                droptCredi = instruccion.ExecuteReader()
                Return droptCredi
                droptCredi.Close()
            End Using
        End Function
        Public Function valorMecanismo(ByVal formCombo As ComboBox) As String
            Try
                cadenaConexBD()
                Dim instruccion As New OleDbCommand("select vaop_mec from mecanismo_vta where opt_mec = '" & formCombo.SelectedItem & "'", cadenaConexBD)
                Dim obj As Object = instruccion.ExecuteScalar()
                valorMecanismo = CType(obj, String)
                If valorMecanismo Is Nothing Then
                    valorMecanismo = "Inexistente"
                End If
            Catch ex As Exception
                valorMecanismo = "Error"
            Finally
                cadenaConexBD.Close()
            End Try
        End Function

        '+++++++++++++ Correspondiente a la tabla pide_vta +++++++++++++
        Public Function optPide() As OleDbDataReader

            Using cadenaConexBD()
                Dim droptCi As OleDbDataReader
                Dim instruccion As New OleDbCommand("select id_pide,opt_pide,vaop_pide from pide_vta", cadenaConexBD)
                droptCi = instruccion.ExecuteReader()
                Return droptCi
                droptCi.Close()
            End Using
        End Function
        Public Function valorPide(ByVal formCombo As ComboBox) As String
            Try
                cadenaConexBD()
                Dim instruccion As New OleDbCommand("select vaop_pide from pide_vta where opt_pide = '" & formCombo.SelectedItem & "'", cadenaConexBD)
                Dim obj As Object = instruccion.ExecuteScalar()
                valorPide = CType(obj, String)
                If valorPide Is Nothing Then
                    valorPide = "Inexistente"
                End If
            Catch ex As Exception
                valorPide = "Error"
            Finally
                cadenaConexBD.Close()
            End Try
        End Function
    End Class
    '**********************************************************************************************************************
    'Clase de insercion de datos en la base de datos
    '**********************************************************************************************************************
    Public Class C_agregarDatosVentas
        Public Sub guardarLLamada()
            Try
                cadenaConexBD()
                Using cadenaConexBD()
                    'id tipo de llamada 
                    Dim buscaridTllamada As New OleDbCommand("Select id_tllam from tipo_llamada where desc_tllam = '" & frmVentas.cbo_tllamada.SelectedItem & "'", cadenaConexBD)
                    Dim idtllamada = buscaridTllamada.ExecuteScalar()
                    'id de la empresa
                    Dim buscaridEmpresa As New OleDbCommand("Select id_empr from empresa where nomb_empr = '" & frmVentas.cbo_empresa.SelectedItem & "'", cadenaConexBD)
                    Dim idEmpresa = buscaridEmpresa.ExecuteScalar()
                    'id auditor 
                    Dim buscaridAuditor As New OleDbCommand("Select id_au from auditor where noap_au = '" & frmVentas.cbo_controla.SelectedItem & "'", cadenaConexBD)
                    Dim idAuditor = buscaridAuditor.ExecuteScalar()
                    'id auditado
                    Dim buscaridAuditado As New OleDbCommand("Select id_ado from auditado where noap_ado = '" & frmVentas.cbo_auditado.SelectedItem & "'", cadenaConexBD)
                    Dim idAuditado = buscaridAuditado.ExecuteScalar()
                    'inserccion de datos a la tabla
                    Dim instruccion As New OleDbCommand("Insert into llamada_vta (fau_llam, fec_llam, nlot_llam, id_tllam, id_empr, id_au, id_ado, vaop_salu, vaop_emp, vaop_ind, vaop_man, vaop_ent, vaop_spe, vaop_pre, vaop_mec, vaop_pide, tfav_llam, tctra_llam, idunico_llam, vato_llam,caso_llam) values ('" & frmVentas.dtp_hoy.Value & "','" & frmVentas.dtp_fllam.Value & "','" & (frmVentas.cboLoteLetra.Text + frmVentas.cboLoteNum.Text) & "'," & idtllamada & "," & idEmpresa & "," & idAuditor & "," & idAuditado & ",'" & frmVentas.txtv01.Text & "','" & frmVentas.txtv02.Text & "','" & frmVentas.txtv03.Text & "','" & frmVentas.txtv04.Text & "','" & frmVentas.txtv05.Text & "','" & frmVentas.txtv06.Text & "','" & frmVentas.txtv07.Text & "','" & frmVentas.txtv08.Text & "','" & frmVentas.txtv09.Text & "','" & frmVentas.rtxt_afavor.Text & "','" & frmVentas.rtxt_encontra.Text & "','" & frmVentas.lblIdunicoLlam.Text & "','" & frmVentas.lbl_puntaje.Text & "','" & frmVentas.txtCaso.Text & "' )", cadenaConexBD)
                    instruccion.ExecuteNonQuery()
                End Using
                MsgBox("Auditoria guardada con Éxito", MsgBoxStyle.Information, "Auditoria")
            Catch ex As Exception
                MessageBox.Show(ex.Message)
            End Try
        End Sub
    End Class

    '**********************************************************************************************************************
    'Funciones para cargar opciones y valores a los combos del formulario de ventas
    '**********************************************************************************************************************
    Public Sub CargarOptSaluVta(ByVal lista As ComboBox)
        lista.Items.Clear()
        Dim optS As New C_todosVentas
        Dim droptS As OleDbDataReader = optS.optSaludo

        If droptS Is Nothing Then
            Exit Sub
        End If
        Dim strloc As String
        While droptS.Read()
            strloc = ""
            If Not droptS.IsDBNull(0) Then
                strloc = droptS.GetString(1)
            End If
            lista.Items.Add(strloc)
        End While
        droptS.Close()
    End Sub
    Public Sub CargarOptEmpaVta(ByVal lista As ComboBox)
        lista.Items.Clear()
        Dim optE As New C_todosVentas
        Dim droptE As OleDbDataReader = optE.optEmpatia

        If droptE Is Nothing Then
            Exit Sub
        End If
        Dim strloc As String
        While droptE.Read()
            strloc = ""
            If Not droptE.IsDBNull(0) Then
                strloc = droptE.GetString(1)
            End If
            lista.Items.Add(strloc)
        End While
        droptE.Close()
    End Sub
    Public Sub CargarOptIndVta(ByVal lista As ComboBox)
        lista.Items.Clear()
        Dim optind As New C_todosVentas
        Dim droptind As OleDbDataReader = optind.optIndagacion
        If droptind Is Nothing Then
            Exit Sub
        End If
        Dim strloc As String
        While droptind.Read()
            strloc = ""
            If Not droptind.IsDBNull(0) Then
                strloc = droptind.GetString(1)
            End If
            lista.Items.Add(strloc)
        End While
        droptind.Close()
    End Sub
    Public Sub CargarOptManVta(ByVal lista As ComboBox)
        lista.Items.Clear()
        Dim optDisp As New C_todosVentas
        Dim droptDisp As OleDbDataReader = optDisp.optManejo
        If droptDisp Is Nothing Then
            Exit Sub
        End If
        Dim strloc As String
        While droptDisp.Read()
            strloc = ""
            If Not droptDisp.IsDBNull(0) Then
                strloc = droptDisp.GetString(1)
            End If
            lista.Items.Add(strloc)
        End While
        droptDisp.Close()
    End Sub
    Public Sub CargarOptEntVta(ByVal lista As ComboBox)
        lista.Items.Clear()
        Dim optComp As New C_todosVentas
        Dim droptComp As OleDbDataReader = optComp.optEntusiasmo
        If droptComp Is Nothing Then
            Exit Sub
        End If
        Dim strloc As String
        While droptComp.Read()
            strloc = ""
            If Not droptComp.IsDBNull(0) Then
                strloc = droptComp.GetString(1)
            End If
            lista.Items.Add(strloc)
        End While
        droptComp.Close()
    End Sub
    Public Sub CargarOptSpeechVta(ByVal lista As ComboBox)
        lista.Items.Clear()
        Dim optinfo As New C_todosVentas
        Dim droptInfo As OleDbDataReader = optinfo.optSpeech
        If droptInfo Is Nothing Then
            Exit Sub
        End If
        Dim strloc As String
        While droptInfo.Read()
            strloc = ""
            If Not droptInfo.IsDBNull(0) Then
                strloc = droptInfo.GetString(1)
            End If
            lista.Items.Add(strloc)
        End While
        droptInfo.Close()
    End Sub
    Public Sub CargarOptPresVta(ByVal lista As ComboBox)
        lista.Items.Clear()
        Dim optComun As New C_todosVentas
        Dim droptComun As OleDbDataReader = optComun.optPresentacion
        If droptComun Is Nothing Then
            Exit Sub
        End If
        Dim strloc As String
        While droptComun.Read()
            strloc = ""
            If Not droptComun.IsDBNull(0) Then
                strloc = droptComun.GetString(1)
            End If
            lista.Items.Add(strloc)
        End While
        droptComun.Close()
    End Sub
    Public Sub CargarOptMecVta(ByVal lista As ComboBox)
        lista.Items.Clear()
        Dim optCredd As New C_todosVentas
        Dim droptCredd As OleDbDataReader = optCredd.optMecanismo
        If droptCredd Is Nothing Then
            Exit Sub
        End If
        Dim strloc As String
        While droptCredd.Read()
            strloc = ""
            If Not droptCredd.IsDBNull(0) Then
                strloc = droptCredd.GetString(1)
            End If
            lista.Items.Add(strloc)
        End While
        droptCredd.Close()
    End Sub
    Public Sub CargarOptPideVta(ByVal lista As ComboBox)
        lista.Items.Clear()
        Dim optCl As New C_todosVentas
        Dim droptClo As OleDbDataReader = optCl.optPide
        If droptClo Is Nothing Then
            Exit Sub
        End If
        Dim strloc As String
        While droptClo.Read()
            strloc = ""
            If Not droptClo.IsDBNull(0) Then
                strloc = droptClo.GetString(1)
            End If
            lista.Items.Add(strloc)
        End While
        droptClo.Close()
    End Sub
    '**********************************************************************************************************************
    'Obtencion de datos referentes (auditadosXempresa, entre otras)
    '**********************************************************************************************************************
    Public Function asesorXempresaVentas(ByVal emp As String) As OleDbDataReader
        Using cadenaConexBD()
            Dim drAseXempr As OleDbDataReader
            Dim instruccion As New OleDbCommand("SELECT auditado.noap_ado FROM auditado INNER JOIN empresa ON auditado.id_empr = empresa.id_empr WHERE empresa.nomb_empr = '" & emp & "'", cadenaConexBD)
            drAseXempr = instruccion.ExecuteReader()
            Return drAseXempr
            drAseXempr.Close()
        End Using
    End Function
    Public Sub auditadosXempresaVentas(ByVal lista As ComboBox, emp As String)
        lista.Items.Clear()
        Dim drAudi As OleDbDataReader = asesorXempresaVentas(emp)
        If drAudi Is Nothing Then
            Exit Sub
        End If
        Dim strloc As String
        While drAudi.Read()
            strloc = ""
            If Not drAudi.IsDBNull(0) Then
                strloc = drAudi.GetString(0)
            End If
            lista.Items.Add(strloc)
        End While
        drAudi.Close()
    End Sub

   

End Module